import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import javax.xml.crypto.Data;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author: 成金乘
 * @date: 2022/11/20 15:01
 * @description:
 */
  //先将数据库连接的基本步骤封装起来
public class JDBCDataBase {
    //创建数据源
    private static final DataSource datasource = new MysqlDataSource();
    static {
        ((MysqlDataSource)datasource).setURL("jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)datasource).setUser("root");
        ((MysqlDataSource)datasource).setPassword("111111");
    }

    public static void main(String[] args) {
        System.out.println(getConnection());
    }

    //获取数据库连接
    public static Connection getConnection(){
        try{
            return datasource.getConnection();
        }catch(SQLException e){
            throw new RuntimeException("数据库连接失败",e);
        }
    }

    //释放数据资源
    public static void close(ResultSet resultset, Statement statement, Connection connection){
        try{
            if(resultset != null){
                resultset.close();
            }
            if (statement != null){
                statement.close();
            }
            if (connection != null){
                connection.close();
            }
        }catch (SQLException e){
            throw new RuntimeException("资源释放异常",e);
        }
    }

    //将日期在字符串转换为JAVA类型
    public static Timestamp getTimestamp(String dataString){
        try{
            java.util.Date date = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(dataString);
            return new java.sql.Timestamp(date.getTime());
        }catch(ParseException e){
            throw new RuntimeException("日期转换错误"+dataString,e);
        }
    }

}

//insert into borrow_info(book_id,student_id,start_time,end_time) values(10,3,"2019-09-25 17:50","2019-10-25 17:50")
/*
 * 新增貂蝉同学的借阅记录：诗经，从2019年9月25日17:50到2019年10月25日17:50
 */
class Add{
    public static void main(String[] args) {
        //创建数据库连接
        Connection connection = null;
        //描述sql的对象
        PreparedStatement preparedstatement = null;
        try {
            connection = JDBCDataBase.getConnection();
            String sql = "insert into borrow_info(book_id,student_id,start_time,end_time) values(?,?,?,?)";
            preparedstatement = connection.prepareStatement(sql);
            preparedstatement.setInt(1,10);
            preparedstatement.setInt(2,3);
            preparedstatement.setTimestamp(3,JDBCDataBase.getTimestamp("2019-09-25 17:50:00"));
            preparedstatement.setTimestamp(4,JDBCDataBase.getTimestamp("2019-10-25 17:50:00"));
            System.out.println(preparedstatement);
            //执行sql语句
            int row = preparedstatement.executeUpdate();
            System.out.println(row);
        } catch(SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            JDBCDataBase.close(null,preparedstatement,connection);
        }
    }
}

//查询计算机分类下的图书借阅信息
class Select{
    public static void main(String[] args) throws SQLException{
        //创建数据库连接
        Connection connection = null;
        //描述sql的对象
        PreparedStatement preparedStatement = null;
        ResultSet resultset = null;
        try{
            connection = JDBCDataBase.getConnection();
            String sql = "select bk.name as book_name,bk.author as book_author," +
                         "s.name as student_name,bi.start_time,bi.end_time" +
                         " from borrow_info as bi join book as bk on bi.book_id = bk.id" +
                         " join category as c on bk.category_id = c.id" +
                         " join student as s on bi.student_id = s.id" +
                         " where c.name = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"计算机");
            //执行语句
            System.out.println(preparedStatement);
            resultset = preparedStatement.executeQuery();
            while (resultset.next()){
                String bookName = resultset.getString("book_name");
                String bookAuthor = resultset.getString("book_author");
                String  studentName = resultset.getString("student_name");
                Timestamp startTime = resultset.getTimestamp("start_time");
                Timestamp endTime = resultset.getTimestamp("end_time");
                System.out.println(String.format("作者: %s,书名: %s,借阅人: %s,借书时间: %s,还书时间: %s",bookName,bookAuthor,studentName,
                                                                                                    startTime,endTime));
            }
        } catch (SQLException e){
            e.printStackTrace();
        } finally {
            JDBCDataBase.close(resultset,preparedStatement,connection);
        }

    }
}


//修改图书《深入理解Java虚拟机》的价格为61.20
class Revise{
    public static void main(String[] args) {
        //创建数据库连接
        Connection connection = null;
        //描述sql的对象
        PreparedStatement preparedstatement = null;
        try {
            connection = JDBCDataBase.getConnection();
            String sql = "update book set price = ? where name = ?";
            preparedstatement = connection.prepareStatement(sql);
            preparedstatement.setDouble(1,61.20);
            preparedstatement.setString(2,"深入理解Java虚拟机");
            System.out.println(preparedstatement);
            //执行sql语句
            int row = preparedstatement.executeUpdate();
            System.out.println(row);
        } catch(SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            JDBCDataBase.close(null,preparedstatement,connection);
        }
    }
}

//删除id最大的一条借阅记录
class Del{
    public static void main(String[] args){
        //创建数据库连接
        Connection connection = null;
        //描述sql的对象
        PreparedStatement preparedstatement = null;
        try {
            connection = JDBCDataBase.getConnection();
            String sql = "delete from borrow_info where id = (select bi.id from (select max(id) as id from borrow_info) as bi)";
            preparedstatement = connection.prepareStatement(sql);
            System.out.println(preparedstatement);
            //执行sql语句
            int row = preparedstatement.executeUpdate();
            System.out.println(row);
        } catch(SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            JDBCDataBase.close(null, preparedstatement, connection);
        }
    }
}

